﻿using Excel2DbTool.Common;
using Excel2DbTool.Helpers;
using Excel2DbTool.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;

namespace Excel2DbTool.Migrater_New
{

	public class HTableMigrater : SimpleDbTableMigrater
	{
		public static readonly string PrimeKey = SelfFieldNames.FWID;
		public static readonly string GROUPKEY = OrginTableFieldNames.HOUSEID;

		static List<DbFieldMapping> dbFieldMappings = new List<DbFieldMapping>()
		{
			 //has match
			new OracleTableStrField(OrginTableFieldNames.HOUSENO,SelfFieldNames.MPH),
			//new OracleTableStrField(OrginTableFieldNames.LAYER,SelfFieldNames.CH), //层号
			new OracleTableStrField(OrginTableFieldNames.LANDCOMPRESS,SelfFieldNames.HZL), 
			new OracleTableStrField(OrginTableFieldNames.BUILDINGNO,SelfFieldNames.DYH), 
			new OracleTableStrField(OrginTableFieldNames.HOUSEID,SelfFieldNames.HH), 
			new OracleTableStrField(OrginTableFieldNames.BZ,SelfFieldNames.BZ), 

			new OracleTableStrField(OrginTableFieldNames.BUILDNO,SelfFieldNames.ZRZH),
			new OracleTableStrField(OrginTableFieldNames.LAYER,SelfFieldNames.SJC),
			new OracleTableNumField(OrginTableFieldNames.BUILDAREA,SelfFieldNames.SCJZMJ),
			new OracleTableNumField(OrginTableFieldNames.INTERAREA,SelfFieldNames.SCTNJZMJ),
			new OracleTableNumField(OrginTableFieldNames.PUBLICAREA,SelfFieldNames.SCFTJZMJ),

			new OrcleTableFieldMappgingToDbSysDict(OrginTableFieldNames.BUSINESSFILEID,SelfFieldNames.CQZT,DbSysDictManager.Instance.BLZTDict),
			new OrcleTableFieldMappgingToDbSysDict(OrginTableFieldNames.NOWMORTSTATE,SelfFieldNames.DYZT,DbSysDictManager.Instance.BLZTDict),
			new OrcleTableFieldMappgingToDbSysDict(OrginTableFieldNames.NOWSEALSTATE,SelfFieldNames.CFZT,DbSysDictManager.Instance.BLZTDict),
			new OrcleTableFieldMappgingToDbSysDict(OrginTableFieldNames.ISBACKUP,SelfFieldNames.BAZT,DbSysDictManager.Instance.BLZTDict),

			new OrcleTableFieldMappgingToDbSysDict(OrginTableFieldNames.USE,SelfFieldNames.FWYT1,DbSysDictManager.Instance.FWYTDict),
			 new OrcleTableFieldMappgingToDbSysDict(OrginTableFieldNames.STRUCTURE,SelfFieldNames.FWJG,DbSysDictManager.Instance.FWJGDict),
			 new OrcleTableFieldMappgingToDbSysDict(OrginTableFieldNames.ISCOMMERCE,SelfFieldNames.FWLX,DbSysDictManager.Instance.FWLXDict),

			new OrcleTableFieldMappgingToDbSysDict(OrginTableFieldNames.BUILDTYPE,SelfFieldNames.HX,DbSysDictManager.Instance.HXDict),
			 
			 //has match, but has same field refer in same table
			new OracleTableStrFieldReferToOtherFieldInSameTable(OrginTableFieldNames.NONE,SelfFieldNames.MYC, SelfFieldNames.SJC,(sjc)=>{
				return sjc;
			}),
			new OracleTableStrFieldReferToOtherFieldInSameTable(OrginTableFieldNames.NONE,SelfFieldNames.ZZLC, SelfFieldNames.SJC,(sjc)=>{
				return sjc;
			}),
			 new OracleTableStrFieldReferToOtherFieldInSameTable(OrginTableFieldNames.NONE,SelfFieldNames.DYMC, SelfFieldNames.DYH,(dyh)=>{
				 return dyh;
			}),
			new OracleTableStrFieldReferToOtherFieldInSameTable(OrginTableFieldNames.NONE,SelfFieldNames.CH, SelfFieldNames.SJC,(sjc)=>{
				return sjc;
			}),
			new OracleTableStrFieldReferToOtherFieldInSameTable(OrginTableFieldNames.NONE,SelfFieldNames.FWBH, SelfFieldNames.HH,(hh)=>{
				 return hh;
			}),
			new OracleTableStrFieldReferToOtherFieldInSameTable(OrginTableFieldNames.NONE,SelfFieldNames.FWID, SelfFieldNames.HH,(hh)=>{
				 return hh;
			}),
			new OracleTableStrFieldReferToOtherFieldInSameTable(OrginTableFieldNames.NONE,SelfFieldNames.FWXZ, SelfFieldNames.FWLX,(fwlx)=>{
				return fwlx;
			}),
			new OracleTableStrFieldHasFixedVal(OrginTableFieldNames.NONE,SelfFieldNames.HXJG,"1"),
			new OracleTableStrFieldHasFixedVal(OrginTableFieldNames.NONE,SelfFieldNames.MJDW,"1"),
			new OracleTableStrFieldHasFixedVal(OrginTableFieldNames.NONE,SelfFieldNames.QXDM,"150523"),
			//can not be null,but orgin table has no match；so we will not select relative data from orgin table;
			//but will give a default value to  target table
			//new OracleTableIdentityStrField(OrginTableFieldNames.NONE,SelfFieldNames.FWID,12), //KEY

			new OracleTableReferenceKeyField(OrginTableFieldNames.NONE,SelfFieldNames.XMID),
			new OracleTableReferenceKeyField(OrginTableFieldNames.NONE,SelfFieldNames.ZRZID),
			new OracleTableReferenceKeyField(OrginTableFieldNames.NONE,SelfFieldNames.LJZID),
			new OracleTableReferenceKeyField(OrginTableFieldNames.NONE,SelfFieldNames.CID),

			new OracleTableNotNullNumField(OrginTableFieldNames.NONE,SelfFieldNames.SFHBCF),
			new OracleTableStrFieldHasFixedVal(OrginTableFieldNames.NONE,SelfFieldNames.SFXZSJ,"1"),
			new OracleTableStrFieldHasFixedVal(OrginTableFieldNames.NONE,SelfFieldNames.HBHS,"1"),
		};


		IList<DbRowMappingNew> c_dbrms;
		DbRowMappingNew ljz_dbrm;
		DbRowMappingNew zrz_dbrm;
		DbRowMappingNew xm_dbrm;
		public HTableMigrater(IList<DbRowMappingNew> c_dbrms, DbRowMappingNew ljz_dbrm, DbRowMappingNew zrz_dbrm, DbRowMappingNew xm_dbrm)
		{
			if (c_dbrms == null || ljz_dbrm == null || zrz_dbrm == null || xm_dbrm == null)
			{
				throw new ArgumentNullException();
			}

			this.c_dbrms = c_dbrms;
			this.ljz_dbrm = ljz_dbrm;
			this.zrz_dbrm = zrz_dbrm;
			this.xm_dbrm = xm_dbrm;
		}

		protected override IList<DbFieldMapping> DbFieldMappings
		{
			get { return dbFieldMappings; }
		}

		IList<DbRowMappingNew> dbRowMappings = null;

		public void Execute(Action<string> transactionInsertAction)
		{
			var strGroup = new List<string>(c_dbrms.Count);

			foreach (var c_dbrm in c_dbrms)
			{

				dbRowMappings = new List<DbRowMappingNew>();

				object val_cPrimiteKey = c_dbrm.GetValByTargetFieldName(CTableMigrater.PrimeKey);

				string sql = GetSelectSqlStr(c_dbrm);

				//对应项目下的自然幢数据下的逻辑幢
				var dt = SqlHelper.GetInstance().Select(sql);

				for (int i = 0; i < dt.Rows.Count; i++)
				{
					DbRowMappingNew dbrm = GetDbRowMapping(val_cPrimiteKey);

					for (int j = 0; j < dt.Columns.Count; j++)
					{
						dbrm[j].Val = dt.Rows[i][j];
					}

					dbRowMappings.Add(dbrm);
				}

				string insertSql = GetInsertSqlString();


				transactionInsertAction(insertSql);
			}
		}

		string GetSelectSqlStr(DbRowMappingNew c_dbrm)
		{
			object xm_projectNameVal = xm_dbrm.GetValByOrginFieldName(XMTableMigrater.GROUPKEY);
			object zrz_buildNoVal = zrz_dbrm.GetValByOrginFieldName(ZRZTableMigrater.GROUPKEY);
			object ljz_buildNoVal = ljz_dbrm.GetValByOrginFieldName(LJZTableMigrater.GROUPKEY);
			object c_layerVal = c_dbrm.GetValByOrginFieldName(CTableMigrater.GROUPKEY);

			string predicateStr = string.Format("{0} = {1} AND {2} = {3} AND {4} = {5} AND {6} ='{7}'", XMTableMigrater.GROUPKEY, xm_projectNameVal, ZRZTableMigrater.GROUPKEY, zrz_buildNoVal, LJZTableMigrater.GROUPKEY, ljz_buildNoVal, CTableMigrater.GROUPKEY, c_layerVal);
			string format = "SELECT {0} FROM {1} where rowid = any (select max(rowid) from (select * from {1} where {2}) group by {3}) ";

			string sql = string.Format(format, GetFieldStringNeedReslove(), ConfigHelper.TableName, predicateStr, GROUPKEY);

			return sql;
		}

		string GetInsertSqlString()
		{
			if (dbRowMappings.Count() == 0)
			{
				return string.Empty;
			}

			StringBuilder sb = new StringBuilder(20000);
			sb.AppendFormat("INSERT INTO {0} ({1})", DbTableNames.BDC_H, GetFieldStringNeedInsert());


			for (int i = 0; i < dbRowMappings.Count(); i++)
			{
				sb.AppendFormat(" SELECT {0} FROM dual {1}", dbRowMappings.ElementAt(i).GetValStr(), (i == dbRowMappings.Count() - 1) ? "" : "union");
			}

			return sb.ToString();
		}

		private DbRowMappingNew GetDbRowMapping(object val_cPrimiteKey)
		{
			DbRowMappingNew dbrm = new DbRowMappingNew();

			foreach (var item in dbFieldMappings)
			{
				if (item is OracleTableReferenceKeyField)
				{
					if (item.TargetFieldName == SelfFieldNames.XMID)
					{
						item.Val = xm_dbrm.GetValByTargetFieldName(SelfFieldNames.XMID);
					}
					else if (item.TargetFieldName == SelfFieldNames.ZRZID)
					{
						item.Val = zrz_dbrm.GetValByTargetFieldName(SelfFieldNames.ZRZID);
					}
					else if (item.TargetFieldName == SelfFieldNames.LJZID)
					{
						item.Val = ljz_dbrm.GetValByTargetFieldName(SelfFieldNames.LJZID);
					}
					else
					{
						item.Val = val_cPrimiteKey;//层
					}
				}
				dbrm.AddFieldMapping(item.Clone());
			}

			return dbrm;
		}


		class SelfFieldNames
		{
			internal static readonly string FWID = "FWID";
			internal static readonly string HH = "HH";
			internal static readonly string FWBH = "FWBH";
			internal static readonly string CH = "CH";
			internal static readonly string DYH = "DYH";
			internal static readonly string DYMC = "DYMC";
			internal static readonly string LJZH = "LJZH";
			internal static readonly string SJC = "SJC";
			internal static readonly string MYC = "MYC";
			internal static readonly string ZZLC = "ZZLC";
			internal static readonly string YCJZMJ = "YCJZMJ";
			internal static readonly string YCTNJZMJ = "YCTNJZMJ";
			internal static readonly string YCFTJZMJ = "YCFTJZMJ";
			internal static readonly string YCDXBFJZMJ = "YCDXBFJZMJ";
			internal static readonly string YCQTJZMJ = "YCQTJZMJ";
			internal static readonly string YCFTXS = "YCFTXS";
			internal static readonly string SCJZMJ = "SCJZMJ";
			internal static readonly string SCTNJZMJ = "SCTNJZMJ";
			internal static readonly string SCFTJZMJ = "SCFTJZMJ";
			internal static readonly string SCDXBFJZMJ = "SCDXBFJZMJ";
			internal static readonly string SCQTJZMJ = "SCQTJZMJ";
			internal static readonly string SCFTXS = "SCFTXS";
			internal static readonly string HX = "HX";
			internal static readonly string HXJG = "HXJG";
			internal static readonly string GYTDMJ = "GYTDMJ";
			internal static readonly string FTTDMJ = "FTTDMJ";
			internal static readonly string DYTDMJ = "DYTDMJ";
			internal static readonly string FWLX = "FWLX";
			internal static readonly string FWXZ = "FWXZ";
			internal static readonly string FWJG = "FWJG";
			internal static readonly string FDCPMT = "FDCPMT";
			internal static readonly string JGSJ = "JGSJ";
			internal static readonly string CQLY = "CQLY";
			internal static readonly string QXDM = "QXDM";
			internal static readonly string MJDW = "MJDW";
			internal static readonly string BLZT = "BLZT";
			internal static readonly string BZ = "BZ";
			internal static readonly string LJZID = "LJZID";
			internal static readonly string CID = "CID";
			internal static readonly string MPH = "MPH";
			internal static readonly string SLID = "SLID";
			internal static readonly string BDCDYH = "BDCDYH";
			internal static readonly string FWYT1 = "FWYT1";
			internal static readonly string FWYT2 = "FWYT2";
			internal static readonly string FWYT3 = "FWYT3";
			internal static readonly string LJZMC = "LJZMC";
			internal static readonly string SLID2 = "SLID2";
			internal static readonly string HZL = "HZL";
			internal static readonly string ZRZH = "ZRZH";
			internal static readonly string XMID = "XMID";
			internal static readonly string BAZT = "BAZT";
			internal static readonly string YGZT = "YGZT";
			internal static readonly string CQZT = "CQZT";
			internal static readonly string YYZT = "YYZT";
			internal static readonly string DYQZT = "DYQZT";
			internal static readonly string DYZT = "DYZT";
			internal static readonly string CFZT = "CFZT";
			internal static readonly string ZRZID = "ZRZID";
			internal static readonly string CJSJ = "CJSJ";
			internal static readonly string CJR = "CJR";
			internal static readonly string XGSJ = "XGSJ";
			internal static readonly string XGR = "XGR";
			internal static readonly string SFHBCF = "SFHBCF";
			internal static readonly string SFXZSJ = "SFXZSJ";
			internal static readonly string KSSYNX = "KSSYNX";
			internal static readonly string JSSYNX = "JSSYNX";
			internal static readonly string ZCS = "ZCS";
			internal static readonly string HBHS = "HBHS";
			internal static readonly string TDYT = "TDYT";
			internal static readonly string QLXZ = "QLXZ";
		}




	}

	/*
	  -- Create table
create table BDC_H
(
  fwid       NVARCHAR2(12) not null,
  hh         NVARCHAR2(20),
  fwbh       NVARCHAR2(30),
  ch         NVARCHAR2(10),
  dyh        NVARCHAR2(10),
  dymc       NVARCHAR2(20),
  ljzh       NVARCHAR2(24),
  sjc        NVARCHAR2(10),
  myc        NVARCHAR2(10),
  zzlc       NVARCHAR2(10),
  ycjzmj     FLOAT,
  yctnjzmj   FLOAT,
  ycftjzmj   FLOAT,
  ycdxbfjzmj FLOAT,
  ycqtjzmj   FLOAT,
  ycftxs     FLOAT,
  scjzmj     FLOAT,
  sctnjzmj   FLOAT,
  scftjzmj   FLOAT,
  scdxbfjzmj FLOAT,
  scqtjzmj   FLOAT,
  scftxs     FLOAT,
  hx         NVARCHAR2(4),
  hxjg       NVARCHAR2(4),
  gytdmj     FLOAT,
  fttdmj     FLOAT,
  dytdmj     FLOAT,
  fwlx       NVARCHAR2(4),
  fwxz       NVARCHAR2(32),
  fwjg       NVARCHAR2(4),
  fdcpmt     NVARCHAR2(200),
  jgsj       DATE,
  cqly       NVARCHAR2(200),
  qxdm       NVARCHAR2(10),
  mjdw       INTEGER,
  blzt       INTEGER,
  bz         NVARCHAR2(200),
  ljzid      NVARCHAR2(20),
  cid        NVARCHAR2(40),
  mph        NVARCHAR2(50),
  slid       CHAR(20),
  bdcdyh     NVARCHAR2(28),
  fwyt1      NVARCHAR2(32),
  fwyt2      NVARCHAR2(32),
  fwyt3      NVARCHAR2(32),
  ljzmc      NVARCHAR2(50),
  slid2      CHAR(20),
  hzl        NVARCHAR2(100),
  zrzh       NVARCHAR2(20),
  xmid       NVARCHAR2(36),
  bazt       NVARCHAR2(20),
  ygzt       NVARCHAR2(20),
  cqzt       NVARCHAR2(20),
  yyzt       NVARCHAR2(20),
  dyqzt      NVARCHAR2(20),
  dyzt       NVARCHAR2(20),
  cfzt       NVARCHAR2(20),
  zrzid      NVARCHAR2(36),
  cjsj       DATE,
  cjr        NVARCHAR2(50),
  xgsj       DATE,
  xgr        NVARCHAR2(50),
  sfhbcf     INTEGER default 0 not null,
  sfxzsj     INTEGER default 1 not null,
  kssynx     DATE,
  jssynx     DATE,
  zcs        INTEGER,
  hbhs       INTEGER default 1 not null,
  tdyt       NVARCHAR2(32),
  qlxz       INTEGER
)
tablespace BDCDJ0616
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
	initial 2M
	next 8K
	minextents 1
	maxextents unlimited
  );
-- Add comments to the columns 
comment on column BDC_H.fwid
  is '房屋ID';
comment on column BDC_H.hh
  is '户号';
comment on column BDC_H.fwbh
  is '房屋编号';
comment on column BDC_H.ch
  is '层号';
comment on column BDC_H.dyh
  is '单元号';
comment on column BDC_H.dymc
  is '单元名称';
comment on column BDC_H.ljzh
  is '逻辑幢号';
comment on column BDC_H.sjc
  is '实际层';
comment on column BDC_H.myc
  is '名义层';
comment on column BDC_H.zzlc
  is '终止楼层';
comment on column BDC_H.ycjzmj
  is '预测建筑面积';
comment on column BDC_H.yctnjzmj
  is '预测套内建筑面积';
comment on column BDC_H.ycftjzmj
  is '预测分摊建筑面积';
comment on column BDC_H.ycdxbfjzmj
  is '预测地下部分建筑面积';
comment on column BDC_H.ycqtjzmj
  is '预测其它建筑面积';
comment on column BDC_H.ycftxs
  is '预测分摊系数';
comment on column BDC_H.scjzmj
  is '实测建筑面积';
comment on column BDC_H.sctnjzmj
  is '实测套内建筑面积';
comment on column BDC_H.scftjzmj
  is '实测分摊建筑面积';
comment on column BDC_H.scdxbfjzmj
  is '实测地下部分建筑面积';
comment on column BDC_H.scqtjzmj
  is '实测其它建筑面积';
comment on column BDC_H.scftxs
  is '实测分摊系数';
comment on column BDC_H.hx
  is '户型';
comment on column BDC_H.hxjg
  is '户型结构';
comment on column BDC_H.gytdmj
  is '共有土地面积';
comment on column BDC_H.fttdmj
  is '分摊土地面积';
comment on column BDC_H.dytdmj
  is '独用土地面积';
comment on column BDC_H.fwlx
  is '房屋类型';
comment on column BDC_H.fwxz
  is '房屋性质';
comment on column BDC_H.fwjg
  is '房屋结构';
comment on column BDC_H.fdcpmt
  is '房地产平面图';
comment on column BDC_H.jgsj
  is '竣工时间';
comment on column BDC_H.cqly
  is '产权来源';
comment on column BDC_H.qxdm
  is '区县代码';
comment on column BDC_H.mjdw
  is '面积单位';
comment on column BDC_H.blzt
  is '办理状态';
comment on column BDC_H.bz
  is '备注';
comment on column BDC_H.ljzid
  is '逻辑幢ID';
comment on column BDC_H.cid
  is '层ID';
comment on column BDC_H.mph
  is '门牌号';
comment on column BDC_H.slid
  is '受理号';
comment on column BDC_H.bdcdyh
  is '不动产单元号';
comment on column BDC_H.fwyt1
  is '房屋用途1';
comment on column BDC_H.fwyt2
  is '房屋用途2';
comment on column BDC_H.fwyt3
  is '房屋用途3';
comment on column BDC_H.ljzmc
  is '逻辑幢名称';
comment on column BDC_H.slid2
  is '受理号2';
comment on column BDC_H.hzl
  is '户坐落';
comment on column BDC_H.zrzh
  is '自然幢号';
comment on column BDC_H.xmid
  is '项目ID';
comment on column BDC_H.bazt
  is '备案状态';
comment on column BDC_H.ygzt
  is '预告状态';
comment on column BDC_H.cqzt
  is '产权状态';
comment on column BDC_H.yyzt
  is '异议状态';
comment on column BDC_H.dyqzt
  is '地役状态';
comment on column BDC_H.dyzt
  is '抵押状态';
comment on column BDC_H.cfzt
  is '查封状态';
comment on column BDC_H.zrzid
  is '自然幢ID';
comment on column BDC_H.cjsj
  is '创建时间';
comment on column BDC_H.cjr
  is '创建人';
comment on column BDC_H.xgsj
  is '修改时间';
comment on column BDC_H.xgr
  is '修改人';
comment on column BDC_H.sfhbcf
  is '是否合并拆分';
comment on column BDC_H.sfxzsj
  is '是否现状数据';
comment on column BDC_H.kssynx
  is '开始使用年限';
comment on column BDC_H.jssynx
  is '结束使用年限';
comment on column BDC_H.zcs
  is '总层数';
comment on column BDC_H.hbhs
  is '每层所占户数';
comment on column BDC_H.tdyt
  is '土地用途';
comment on column BDC_H.qlxz
  is '权利性质';
-- Create/Recreate indexes 
create index BDC_H_ZRZID on BDC_H (ZRZID)
  tablespace BDCDJ0616
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
	initial 128K
	next 1M
	minextents 1
	maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table BDC_H
  add constraint BDC_H primary key (FWID)
  using index 
  tablespace BDCDJ0616
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
	initial 128K
	next 1M
	minextents 1
	maxextents unlimited
  );
	 */

}
